Expeditions Dataset

The expeditions/ dataset anchors the entire spatial structure of the Pristine Seas Science Database. It provides a canonical registry of expeditions, spatial hierarchy, and field deployments across all survey methods. These tables enable consistent joins, filtering, and spatial aggregation across datasets.

1 Tables

1.1 expeditions.info

This table stores metadata for each expedition conducted by the Pristine Seas team. Each row corresponds to a unique expedition and defines the top-level context for all associated sampling efforts.

Table 1: expeditions.info Table Schema
Field Type Required Description
exp_id STRING true Unique expedition identifier in the format ISO3_YEAR (e.g., FJI_2025)
number INTEGER true Sequential expedition number (e.g., 43)
name STRING true Official or working expedition name
country STRING true Primary country or jurisdiction visited
start_date DATE true Expedition start date in ISO 8601 format (YYYY-MM-DD)
end_date DATE true Expedition end date in ISO 8601 format (YYYY-MM-DD)
lead STRING true Overall expedition lead
science_lead STRING true Lead scientist for the research campaign
vessel STRING false Name of the vessel or platform used
ship_track STRING false WKT LINESTRING representing the ship’s route (optional)
partners STRING false Institutional collaborators (optional)
description STRING false Brief description of expedition goals and scope
notes STRING false Optional free-text notes or admin metadata

To date, our team has conducted 47 expeditions across 30 countries since our first one in 2007.

Figure 1: Timeline of expeditions

1.2 expeditions.sites

The expeditions.sites table defines the canonical spatial unit for all Pristine Seas field deployments (Table 2). Each row represents a single application of a survey method (e.g., UVS, pBRUV, submersible) at a specific place and time during an expedition.

While method-specific site tables exist (e.g., uvs.sites, sub.sites), this shared table provides a centralized registry that supports cross-method integration, standardized mapping, and consistent spatial joins.

What is a site?

A site is defined by a unique combination of exp_id, method, and a 3-digit site number
(e.g., FJI_2025_uvs_001)

  • It represents one deployment, diver, or survey event of a given method at a location
  • Each site may contain multiple stations (e.g., depth strata, replicates, rigs)

Examples:

- A **UVS site** could be a reef dive where divers conduct fish, benthic, and invertebrate surveys at multiple depths
- A pBRUV site is a pelagic drop of five video rigs — each a separate station
- A submersible site refers to a single dive, typically comprising multiple horizontal transects

Sites serve as the parent unit of stations and a universal spatial key across all modules.

Table 2: expeditions.sites Table Schema
Field Type Required Description
ps_site_id STRING true Unique site ID (exp_id_method_###), e.g., FJI_2025_uvs_001
exp_id STRING true Foreign key to expeditions.info
method STRING true Field method used (e.g., uvs, pbruv, edna, sub)
region STRING true Broad geographic or administrative unit (e.g., Murat, Chocó, Tuamotu, Duff Islands).
subregion STRING true Intermediate feature within the region such as an atoll, island, gulf, or reef complex
locality STRING false Local named feature such as a village, bay, cove, reef (e.g., Lolieng, Ensenada Utría).
date DATE true Date of the site-level deployment in ISO 8601 format (YYYY-MM-DD)
time TIME true Local time of the deployment in 24-hour format (HH:MM:SS)
latitude FLOAT true Approximate latitude (decimal degrees, WGS84)
longitude FLOAT true Approximate longitude (decimal degrees, WGS84)
lead STRING true Name of the lead scientist or survey team lead
notes STRING false Optional comments, metadata, or field observations